<?php

namespace app\admin\controller;

use app\common\controller\BasicAdmin;
use app\common\model\UserModel;
use app\common\traits\SelectPage;
use think\Db;
use app\common\model\CourseAttendanceModel;
use app\common\model\CourseModel;
use app\common\model\SchoolModel;
use app\common\model\UserTimesModel;

class User extends BasicAdmin
{
    use SelectPage;
    public $model = 'UserModel';

    /**
     * 用户列表
     */
    public function index(){
//        return $this->_query($this->model)
//            ->equal('school_id')
//            ->equal('grade_id')
//            ->equal('class_id')
//            ->like('realname,phone')
//            ->with(['school','schoolgrade','schoolclass'])
//            ->page();
        $query = $this->_query($this->model)
            ->join('edu_user_times t','t.user_id=edu_user.id','LEFT')
            ->join('edu_course c','t.course_id=c.id','LEFT')
            ->join('edu_school_class clas','clas.id=edu_user.class_id','LEFT')
            ->join('edu_school_grade grade','grade.id=edu_user.grade_id','LEFT')
            ->join('edu_user u2','u2.id=edu_user.pid','LEFT')
            ->field('edu_user.*,u2.realname AS bind_name, GROUP_CONCAT(DISTINCT c.name) as course_name, clas.name as class_name, grade.name as grade_name')
            ->group('edu_user.id');
        if(trim(input('realname'))){
            $query->where('edu_user.realname','like','%'.trim(input('realname')).'%');
        }
        if(trim(input('phone'))){
            $query->where('edu_user.phone','like','%'.trim(input('phone')).'%');
        }
        if(trim(input('student_no'))){
            $query->where('edu_user.student_no','like','%'.trim(input('student_no')).'%');
        }
        if(trim(input('school_id'))){
            $query->where('edu_user.school_id','=',trim(input('school_id')));
        }
        if(trim(input('grade_id'))){
            $query->where('edu_user.grade_id','=',trim(input('grade_id')));
        }
        if(trim(input('class_id'))){
            $query->where('edu_user.class_id','=',trim(input('class_id')));
        }
        if(trim(input('course_id'))){
            $query->where('c.id','=',trim(input('course_id')));
        }
        return $query->page();
    }

    /**
     * 详情
     */
    public function detail(){
        $user_id = $this->request->get('id');
        $user =  UserModel::where('id',$user_id)->with(['school','schoolgrade','schoolclass'])->find();
        //学生
        if($user['role']==1){
            //常规
            $user['normal'] = CourseModel::where('status',1)
                ->where('type',1)
                ->where('school_id',$user['school_id'])
                ->where('grade_id='.$user['grade_id'].' or grade_id is null')
                ->where('class_id='.$user['class_id'].' or class_id is null')
                ->select();
            //兴趣
            $course_ids = UserTimesModel::where('user_id',$user['id'])
                ->field('group_concat(course_id) as course_ids')->find()['course_ids'];
            $user['interest'] = CourseModel::where('status',1)
                ->where('type',2)
                ->where('id','in',$course_ids)
                ->select();
            //考勤
            $attendance = CourseAttendanceModel::where('find_in_set(in_userids,'.$user_id.')')->select();
            foreach ($attendance as $key=>$value){
                $value['course_name'] = CourseModel::where('id',$value['course_id'])->value('name');
            }
            $user['attendance'] = $attendance;
            //获取学生的学习时长与次数
            $user['count'] = CourseAttendanceModel::where('find_in_set('.$user['id'].',in_userids)')->count();
            $user['time'] =  ceil(45*$user['count']/60);
        }
        //教师
        if($user['role']==2){
            //常规
            $user['normal'] = CourseModel::where('status',1)
                ->where('type',1)
                ->where('teacher_id',$user['id'])
                ->select();
            //兴趣
            $course_ids = UserTimesModel::where('teacher_id',$user['id'])
                ->field('group_concat(course_id) as course_ids')->find()['course_ids'];
            $user['interest'] = CourseModel::where('status',1)
                ->where('type',2)
                ->where('id','in',$course_ids)
                ->select();
            //考勤
            $attendance = CourseAttendanceModel::where('teacher_id',$user['id'])->select();
            foreach ($attendance as $key=>$value){
                $value['course_name'] = CourseModel::where('id',$value['course_id'])->value('name');
            }
            $user['attendance'] = $attendance;
            //获取教师的授课时长与次数
            $user['count'] = CourseAttendanceModel::where('teacher_id',$user['id'])->count();
            $user['time'] =  ceil(45*$user['count']/60);
        }
        //班主任
        if($user['role']==3){
            //班主任的话计算学生数与课程数
            $user['student_count'] = UserModel::where('class_id',$user['class_id'])
                ->where('role',1)->count();
            $user['course_count'] =  CourseModel::where('class_id',$user['class_id'])->count();
            //缺勤率
            $course_ids = CourseModel::where('class_id',$user['class_id'])
                ->field('group_concat(id) as course_ids')->find()['course_ids'];
            $attendance_count = CourseAttendanceModel::where('course_id','in',$course_ids)->count();
            $attendance = CourseAttendanceModel::where('course_id','in',$course_ids)->select();
            $rate = 0;
            foreach ($attendance as $key=>&$value){
                if(count(explode(',',$value['all_userids']))!=0){
                    $rate += count(explode(',',$value['away_userids']))/count(explode(',',$value['all_userids']));
                }
            }
            if($attendance_count!=0){
                $attendance_rate = $rate/$attendance_count;
            }else{
                $attendance_rate = 0 ;
            }
            $user['attendance_rate'] = round($attendance_rate,4)*100;
        }
        //校长
        if($user['role']==4) {
            //校长的话计算学生数与班级数与缺勤率
            $user['student_count'] = UserModel::where('school_id', $user['school_id'])
                ->where('role', 1)->count();
            $user['class_count'] = UserModel::where('school_id', $user['school_id'])
                ->group('class_id')
                ->count();
            //缺勤率
            $course_ids = CourseModel::where('school_id', $user['school_id'])
                ->field('group_concat(id) as course_ids')->find()['course_ids'];
            $attendance_count = CourseAttendanceModel::where('course_id', 'in', $course_ids)
                ->where('away_userids', 'neq', null)
                ->where('all_userids', 'neq', null)
                ->count();
            $attendance = CourseAttendanceModel::where('course_id', 'in', $course_ids)->select();
            $rate = 0;
            foreach ($attendance as $key => &$value) {
                if ($value['away_userids'] != null && $value['all_userids'] != null && count(explode(',', $value['all_userids'])) != 0) {
                    $rate += count(explode(',', $value['away_userids'])) / count(explode(',', $value['all_userids']));
                }
            }
            if($attendance_count!=0){
                $attendance_rate = $rate/$attendance_count;
            }else{
                $attendance_rate = 0 ;
            }
            $user['attendance_rate'] = round($attendance_rate, 4) * 100;
        }
        $this->assign('user',$user);
        $this->fetch();
    }

    /**
     * 设置角色
     */
    public function set_role(){
        $user_id = $this->request->post('id');
        $role = $this->request->post('role');
        $this->_save($this->model, [
            'role' => $role,
        ],'id', ['id' => $user_id]);
    }
    
     /**
     * 设置真实姓名
     */
    public function set_realname()
    {
        $this->title = '编辑';
        return $this->_form($this->model, 'realname');
    }

    /**
     * 设置手机号
     */
    public function set_phone()
    {
        $this->title = '编辑';
        return $this->_form($this->model, 'phone');
    }
    
    /**
     * 编辑
     */
    public function edit()
    {
        $this->title = '编辑';
        return $this->_form($this->model, 'form');
    }

    public function _edit_form_filter(&$data)
    {
        if ($this->request->isPost()) {
            $data['school_name'] = SchoolModel::where('id',$data['school_id'])->value('name');
        }
    }

    public function export_student_form()
    {
        return $this->_form($this->model, 'export_form');
    }

    public function export_student()
    {
        $list = Db::table('edu_user')->alias('a')
            ->leftJoin('edu_user_times b','a.id = b.user_id')->where(function ($db){
                $db->where('a.role',1);
                if($this->request->has('school_id','get') && !empty($this->request->get('school_id'))){
                    $school_id = $this->request->get('school_id');
                    $db->where('a.school_id',$school_id);
                }
                if($this->request->has('class_id','get') && !empty($this->request->get('class_id'))){
                    $class_id = $this->request->get('class_id');
                    $db->where('a.class_id',$class_id);
                }
                if($this->request->has('course_id','get') && !empty($this->request->get('course_id'))){
                    $course_id = $this->request->get('course_id');
                    $db->where('b.course_id', $course_id);
                }
            })
            ->select();

        foreach ($list as $key => $item)
        {
            if($item['gender']=='1') {
                $list[$key]['_gender'] = '男';
            }elseif($item['gender']=='2') {
                $list[$key]['_gender'] = '女';
            }else{
                $list[$key]['_gender'] = '未知';
            }
            $list[$key]['_grade'] = $this->request->get('grade_id_text');
            $list[$key]['_class'] = $this->request->get('class_id_text');
            $list[$key]['_course'] = $this->request->get('course_id_text');

//            if($this->request->has('grade_id','get') && !empty($this->request->get('grade_id'))) {
//                $grade_id = $this->request->get('grade_id');
//                $list[$key]['_grade'] = SchoolGradeModel::where('id',$grade_id)->value('name');
//            }else {
//                $list[$key]['_grade'] = '';
//            }
//
//            if($this->request->has('class_id','get') && !empty($this->request->get('class_id'))){
//                $class_id = $this->request->get('class_id');
//                $list[$key]['_class'] = SchoolClassModel::where('id',$class_id)->value('name');
//            }else {
//                $list[$key]['_class'] = '';
//            }
//
//            if($this->request->has('course_id','get') && !empty($this->request->get('course_id'))){
//                $course_id = $this->request->get('course_id');
//                $list[$key]['_course'] = CourseModel::where('id', $course_id)->value('name');
//            }else {
//                $list[$key]['_course'] = '';
//            }
        }

        $table_field = [
            ['A' ,'nickname', '用户昵称'],
            ['B' ,'realname','真实姓名'],
            ['C' ,'_gender','性别'],
            ['D' ,'student_no', '学生号'],
            ['E' ,'phone', '手机号'],
            ['F' ,'school_name','学校名称'],
            ['G' ,'_grade','年级名称'],
            ['H' ,'_class','班级名称'],
            ['I' ,'_course','课程名称'],
        ];

        return $this->_export($list, $table_field, "用户数据");
    }
}